﻿//====================================================================
// Create by Daniel
// file： YCUserRole.cs
// project ：YunCMS
// Build time：2012-09-22
// modify:
// description:
// ===================================================================
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using YunCMS.Model;
using YunCMS.IDAL;
//using Utility;

namespace YunCMS.DAL
{
    /// <summary>
    /// Data Access Layer of dbo.YC_User_Role.
    /// </summary>
    public partial class YCUserRoleDAL : IYCUserRoleDAL
    {
		#region 
		
		/// <summary>
		/// constractor
		/// </summary>
		public YCUserRoleDAL()
		{
		}
		#endregion

        #region -----------Methods-----------
		/// <summary>
		/// Insert a model to Database
		/// </summary>
		/// <param name="model">YCUserRolenew model</param>
		/// <returns>The new item Id</returns>
		public int Add(YCUserRole model)
		{
			StringBuilder sqlStr = new StringBuilder();
			sqlStr.Append("insert into YC_User_Role(");
            sqlStr.Append("Name,Note,DisplayName,Type)");
            sqlStr.Append(" values (");
            sqlStr.Append("@Name,@Note,@DisplayName,@Type)");
            sqlStr.Append(";select @@IDENTITY");
			int res;

            res = SqlHelper.ExecuteNonQuery(CommandType.Text, sqlStr.ToString(), PrepareSqlParams(model, false, false));
			return res;
		}

        public int AddWithActions(YCUserRole model)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("insert into YC_User_Role(");
            sqlStr.Append("Name,Note,DisplayName,Type)");
            sqlStr.Append(" values (");
            sqlStr.Append("@Name,@Note,@DisplayName,@Type)");
            sqlStr.Append(";set @ReturnValue= @@IDENTITY");

            StringBuilder sqlStr2 = new StringBuilder();
            sqlStr2.Append("insert into YC_User_RoleActions(");
            sqlStr2.Append("RoleID,ActionID,Note)");
            sqlStr2.Append(" values (");
            sqlStr2.Append("@RoleID,@ActionID,@Note)");

            YCUserRoleActionsDAL actionDal = new YCUserRoleActionsDAL();

            using (SqlConnection connection = new SqlConnection(SqlHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                int indentity = 0;
                SqlTransaction transaction = connection.BeginTransaction("SampleTransaction");
                try
                {
                    SqlParameter[] params1 = PrepareSqlParams(model, false, true);
                    SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sqlStr.ToString(), params1);
                    indentity = Convert.ToInt32(params1[4].Value);

                    foreach (YCUserRoleActions action in model.Actions)
                    {
                        SqlParameter[] params2 = PrepareActionSqlParams(action, false);
                        params2[0].Value = indentity;
                        SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sqlStr2.ToString(), params2);
                    }
                    // Attempt to commit the transaction.
                    transaction.Commit();
                    return indentity;
                }
                catch
                {
                    // Attempt to roll back the transaction.
                    try
                    {
                        transaction.Rollback();
                        return 0;
                    }
                    catch
                    {
                        return -1;
                    }
                }
                finally
                {
                    connection.Close();
                }
            }
        }

		/// <summary>
		/// Insert a model to Database in transaction.
		/// </summary>
        /// <param name="trans">SqlTransaction</param>
		/// <param name="model">YCUserRolenew model</param>
		/// <returns>The new item Id</returns>
		public int Add(SqlTransaction trans,YCUserRole model)
		{
			StringBuilder sqlStr = new StringBuilder();
			sqlStr.Append("insert into YC_User_Role(");
            sqlStr.Append("Name,Note,DisplayName,Type)");
            sqlStr.Append(" values (");
            sqlStr.Append("@Name,@Note,@DisplayName,@Type)");
			int res;			
			
			res = SqlHelper.ExecuteNonQuery(trans,CommandType.Text,sqlStr.ToString(),PrepareSqlParams(model, false, false));
			return res;
		}
		/// <summary>
		/// update a model to db
		/// </summary>
		/// <param name="model">YCUserRole</param>
		/// <returns>rows affected</returns>
		public int Update(YCUserRole model)
		{
            StringBuilder sqlStr = new StringBuilder();
			sqlStr.Append("update YC_User_Role set ");
            sqlStr.Append("Name=@Name,Note=@Note,DisplayName=@DisplayName,Type=@Type");
			sqlStr.Append(" where ID=@ID");
			int res;

            res = SqlHelper.ExecuteNonQuery(CommandType.Text, sqlStr.ToString(), PrepareSqlParams(model, true, false));
			return res;
		}

        public int UpdateWithActions(YCUserRole model)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("update YC_User_Role set ");
            sqlStr.Append("Name=@Name,Note=@Note,DisplayName=@DisplayName,Type=@Type");
            sqlStr.Append(" where ID=@ID");

            StringBuilder sqlStr2 = new StringBuilder();
            sqlStr2.Append("insert into YC_User_RoleActions(");
            sqlStr2.Append("RoleID,ActionID,Note)");
            sqlStr2.Append(" values (");
            sqlStr2.Append("@RoleID,@ActionID,@Note)");

            string delStr = "delete from YC_User_RoleActions where RoleID=@RoleID";

            YCUserRoleActionsDAL actionDal = new YCUserRoleActionsDAL();

            using (SqlConnection connection = new SqlConnection(SqlHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction("SampleTransaction");
                try
                {
                    SqlParameter[] params1 = PrepareSqlParams(model, true, true);
                    SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sqlStr.ToString(), params1);

                    SqlParameter[] parameters ={
							new SqlParameter("@RoleID",SqlDbType.Int)			
			        };
                    parameters[0].Value = model.ID;
                    SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, delStr.ToString(), parameters);

                    foreach (YCUserRoleActions action in model.Actions)
                    {
                        action.RoleID = model.ID;
                        SqlParameter[] params2 = PrepareActionSqlParams(action, true);
                        SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sqlStr2.ToString(), params2);
                    }
                    // Attempt to commit the transaction.
                    transaction.Commit();
                    return model.ID;
                }
                catch
                {
                    // Attempt to roll back the transaction.
                    try
                    {
                        transaction.Rollback();
                        return 0;
                    }
                    catch
                    {
                        return -1;
                    }
                }
                finally
                {
                    connection.Close();
                }
            }
        }

		
        /// <summary>
		/// update a model to db in transaction
		/// </summary>
        /// <param name="trans">SqlTransaction</param>
		/// <param name="model">YCUserRole</param>
		/// <returns>rows affected</returns>
		public int Update(SqlTransaction trans,YCUserRole model)
		{
            StringBuilder sqlStr = new StringBuilder();
			sqlStr.Append("update YC_User_Role set ");
            sqlStr.Append("Name=@Name,Note=@Note,DisplayName=@DisplayName,Type=@Type");
			sqlStr.Append(" where ID=@ID");
			int res;

            res = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sqlStr.ToString(), PrepareSqlParams(model, true, false));
			return res;
		}
		
		/// <summary>
		/// delete a model from db
		/// </summary>
	    /// <param name="id">ID</param>
		/// <returns>rows affected</returns>
		public int Delete(int id)
		{
			StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("delete YC_User_Role ");
            sqlStr.Append(" where ID=@ID");
			SqlParameter[] parameters={
							new SqlParameter("@ID",SqlDbType.Int),
			
			};
			parameters[0].Value=id;
			return SqlHelper.ExecuteNonQuery(CommandType.Text,sqlStr.ToString(),parameters);
		}
		
        /// <summary>
		/// delete a model from db in transaction
		/// </summary>        
        /// <param name="trans">SqlTransaction</param>
	    /// <param name="id">ID</param>
		/// <returns>rows affected</returns>
		public int DeleteWithActions(int id)
		{
            string sqlStr = "delete YC_User_Role where ID=@ID";
            string delStr = "delete from YC_User_RoleActions where RoleID=@ID";
			SqlParameter[] parameters={
							new SqlParameter("@ID",SqlDbType.Int)			
			};
			parameters[0].Value=id;

            using (SqlConnection connection = new SqlConnection(SqlHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction("SampleTransaction");
                try
                {
                    SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sqlStr.ToString(), parameters);
                    SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, delStr.ToString(), parameters);
                    transaction.Commit();
                    return 1;
                }
                catch
                {
                    // Attempt to roll back the transaction.
                    try
                    {
                        transaction.Rollback();
                        return 0;
                    }
                    catch
                    {
                        return -1;
                    }
                }
                finally
                {
                    connection.Close();
                }
            }
		}
		
        /// <summary>
		///Get YCUserRole Entity
		/// </summary>
		/// <param name="dr">dr</param>
		/// <returns>YCUserRole Entity</returns>
		public YCUserRole PopulateYCUserRoleFromDr(IDataReader dr)
		{
			YCUserRole Obj = new YCUserRole();
			
				Obj.ID = Convert.ToInt32( dr["ID"]);
				Obj.Name =  dr["Name"].ToString();
                Obj.Note =  dr["Note"].ToString();
                Obj.DisplayName =  dr["DisplayName"].ToString();
				Obj.Type = Convert.ToInt32( dr["Type"]);
			
			return Obj;
		}

        public YCUserRole GetYCUserRole(int id)
        {
            YCUserRole _obj = null;
            SqlParameter[] parameters ={
			    new SqlParameter("@ID",SqlDbType.Int)
			};
            parameters[0].Value = id;
            string sqlStr = "select * from YC_User_Role where ID=@ID";
            using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sqlStr, parameters))
            {
                while (dr.Read())
                {
                    _obj = PopulateYCUserRoleFromDr(dr);
                }
            }

            return _obj;
        }
		
		/// <summary>
		/// get a YCUserRole object 
		/// </summary>
		/// <param name=id>ID</param>
		/// <returns>YCUserRole object</returns>
		public YCUserRole GetYCUserRoleWithActions (int id)
		{
			YCUserRole _obj=null;
            IList<YCUserRoleActions> Obj = new List<YCUserRoleActions>();
			SqlParameter[] parameters={
			    new SqlParameter("@ID",SqlDbType.Int)
			};
			parameters[0].Value=id;
			string sqlStr="select * from YC_User_Role where ID=@ID";
            string sqlStr2 = "select a.*, b.Name,b.ActionType,b.Module,b.Category,b.ActionValue,b.PageUrl from YC_User_RoleActions a left join YC_User_Action b on a.ActionID = b.ID where a.RoleID=@ID";
			using(SqlDataReader dr=SqlHelper.ExecuteReader(CommandType.Text,sqlStr,parameters))
			{
				while(dr.Read())
				{
            		_obj = PopulateYCUserRoleFromDr(dr);
				}
			}

            using (SqlDataReader dr2 = SqlHelper.ExecuteReader(CommandType.Text, sqlStr2, parameters))
            {
                while (dr2.Read())
                {
                    Obj.Add(PopulateYCUserRoleActionsFromDr(dr2));
                }
            }
            _obj.Actions = Obj;
			return _obj;
		}
		/// <summary>
		/// Get YCUserRoles List
		/// </summary>
		/// <returns>List</returns>
        public IList<YCUserRole> GetList(string name)
		{
			IList<YCUserRole> Obj=new List<YCUserRole>();
            SqlParameter[] parameters ={
			    new SqlParameter("@Name",SqlDbType.NVarChar)
			};
            parameters[0].Value = name;
            string sqlStr = "select * from YC_User_Role where Name like @Name+'%'";
            using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sqlStr, parameters))
			{
				while(dr.Read())
				{
					Obj.Add(PopulateYCUserRoleFromDr(dr));
				}
			}
			return Obj;
		}

        public IList<YCUserRole> GetList()
        {
            IList<YCUserRole> Obj = new List<YCUserRole>();

            string sqlStr = "select * from YC_User_Role";
            using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sqlStr))
            {
                while (dr.Read())
                {
                    Obj.Add(PopulateYCUserRoleFromDr(dr));
                }
            }
            return Obj;
        }

		/// <summary>
        /// if exist a YCUserRole according by ID
        /// </summary>
        /// <param name="id">id</param>
        /// <returns>true/false</returns>
		public bool IsExist(int id)
		{
			SqlParameter[] parameters={
                                      new SqlParameter("@ID",SqlDbType.Int)
                                  };
            parameters[0].Value=id;
            string sqlStr="select Count(1) from YC_User_Role where ID=@ID";
            int a=Convert.ToInt32(SqlHelper.ExecuteScalar(CommandType.Text,sqlStr,parameters));
            if(a>0)
            {
                return true;
            }
            else
            {
                return false;
            }
		}

        /// <summary>
        ///Get YCUserRoleActions Entity
        /// </summary>
        /// <param name="dr">dr</param>
        /// <returns>YCUserRoleActions Entity</returns>
        public YCUserRoleActions PopulateYCUserRoleActionsFromDr(IDataReader dr)
        {
            YCUserRoleActions Obj = new YCUserRoleActions();

            Obj.ID = Convert.ToInt32(dr["ID"]);
            Obj.RoleID = Convert.ToInt32(dr["RoleID"]);
            Obj.ActionID = Convert.ToInt32(dr["ActionID"]);
            Obj.Note = dr["Note"].ToString();

            Obj.Name = dr["Name"].ToString();
            Obj.ActionType = dr["ActionType"].ToString();
            Obj.Module = dr["Module"].ToString();
            if ((dr["Category"]) == DBNull.Value) { Obj.Category = null; } else { Obj.Category = Convert.ToInt32(dr["Category"]); }
            Obj.ActionValue = dr["ActionValue"].ToString();
            Obj.PageUrl = dr["PageUrl"].ToString();

            return Obj;
        }
        
        private SqlParameter[] PrepareSqlParams(YCUserRole model, bool isUpdate, bool isTran)
        {
     			 List<SqlParameter> parameters = new List<SqlParameter>() {							
							new SqlParameter("@Name",SqlDbType.NVarChar),
							new SqlParameter("@Note",SqlDbType.NVarChar),
							new SqlParameter("@DisplayName",SqlDbType.NVarChar),
							new SqlParameter("@Type",SqlDbType.Int)
			};
                parameters[0].Value = model.Name;                
			if(model.Note!= String.Empty)
				parameters[1].Value = model.Note;
			else
				parameters[1].Value = DBNull.Value;				
			if(model.DisplayName!= String.Empty)
				parameters[2].Value = model.DisplayName;
			else
				parameters[2].Value = DBNull.Value;				
                parameters[3].Value = model.Type;                
            
            if (isUpdate)
            {
                SqlParameter param = new SqlParameter("@ID",SqlDbType.Int);
                param.Value = model.ID; 
                parameters.Add(param);
            }
            else if (isTran)
            {
                SqlParameter param = new SqlParameter("@ReturnValue",SqlDbType.Int);
                param.Direction = ParameterDirection.Output;
                parameters.Add(param);
            }

            return parameters.ToArray();
        }

        private SqlParameter[] PrepareActionSqlParams(YCUserRoleActions model, bool isUpdate)
        {
            List<SqlParameter> parameters = new List<SqlParameter>() {							
							new SqlParameter("@RoleID",SqlDbType.Int),
							new SqlParameter("@ActionID",SqlDbType.Int),
							new SqlParameter("@Note",SqlDbType.NVarChar)
			};
            
            parameters[1].Value = model.ActionID;
            if (model.Note != String.Empty)
                parameters[2].Value = model.Note;
            else
                parameters[2].Value = DBNull.Value;

            if (isUpdate)
            {
                parameters[0].Value = model.RoleID;
            }
            else
            {
                parameters[0].Direction = ParameterDirection.InputOutput;
            }

            return parameters.ToArray();
        }
        
        #endregion
		
    }
}




